#!/usr/bin/python
#-*- coding: utf-8 -*-
# 抽取oracle数据到redis_mq模块
# 作者：王成
# 日期：2017-05-03
import redis
import requests
import json
import yaml,logging
from logging.handlers import TimedRotatingFileHandler,RotatingFileHandler
import time,timeit,datetime,sys,os,threading
import Queue
from daemon import Daemon
import traceback
import StringIO
import cx_Oracle
import pickle
import pylru
import pickle
import MySQLdb

reload(sys)
sys.setdefaultencoding('utf-8')
os.environ['NLS_LANG']="SIMPLIFIED CHINESE_CHINA.AL32UTF8"

class MyDaemon(Daemon):

    def execute_sql(self,sql,action='select'):
        try:
            if self.db is None:
                self.db = MySQLdb.connect(host=self.config['mysql_web']['host'],port=self.config['mysql_web']['port'], user=self.config['mysql_web']['user'], passwd=self.config['mysql_web']['password'], db=self.config['mysql_web']['database'],charset="utf8")
            try:
                self.db.ping()
            except MySQLdb.Error,e:
                self.db = MySQLdb.connect(host=self.config['mysql_web']['host'],port=self.config['mysql_web']['port'], user=self.config['mysql_web']['user'], passwd=self.config['mysql_web']['password'], db=self.config['mysql_web']['database'],charset="utf8")

            mysql_web = self.db.cursor(MySQLdb.cursors.DictCursor)
            r = mysql_web.execute(sql)
            if action=='select':
                r = mysql_web.fetchall()
            elif action=='update':
                pass
            elif action=='insert':
                r = self.db.insert_id()
            mysql_web.close()
            return r
        except Exception, e:
            logging.exception('连接数据库时错误: %s', str(e))
            r = None
            if action=='select':
                r = []
            elif action=='update':
                pass
            elif action=='insert':
                r = 0
            return r

    def load_cache(self):
        self.device_to_yisa_device = {}#第三方设备ID<=>Yisa设备ID映射
        if os.path.isfile(os.path.dirname(os.path.abspath(__file__))+"/data/device_dict.dat"):
            with open(os.path.dirname(os.path.abspath(__file__))+"/data/device_dict.dat") as f:
                d_str = f.read()
                self.device_to_yisa_device = pickle.loads(d_str)
        self.location_to_yisa_location = {}#第三方卡口ID<=>Yisa卡口ID映射
        if os.path.isfile(os.path.dirname(os.path.abspath(__file__))+"/data/location_dict.dat"):
            with open(os.path.dirname(os.path.abspath(__file__))+"/data/location_dict.dat") as f:
                d_str = f.read()
                self.location_to_yisa_location = pickle.loads(d_str)

        # self.yisa_device2yisa_location = {}#Yisa设备ID<=>Yisa卡口映射
        # if os.path.isfile(os.path.dirname(os.path.abspath(__file__))+"/data/device_to_location_dict.dat"):
            # with open(os.path.dirname(os.path.abspath(__file__))+"/data/device_to_location_dict.dat") as f:
                # d_str = f.read()
                # self.yisa_device2yisa_location = pickle.loads(d_str)  
        rows = self.execute_sql("SELECT loc_id,region_code FROM mon_location","select")
        self.location2yisa_region = {}
        for row in rows:
            self.location2yisa_region[row['loc_id']] = row['region_code']#Yisa卡口<=>Yisa区域映射 


    def connect(self):
        while not self.connected:
            try:
                self.connected = False
                self.client = cx_Oracle.connect('ivms_thr/ivms_thr@172.19.111.204:1521/orcl')
            except cx_Oracle.DatabaseError, e:
                #logging.error('Connecting error: %s', str(e))
                print "Connecting error: "+str(e)
                time.sleep(1)
            else:
                #logging.info('Connected to Oracle server %s', '10.53.11.34')
                self.connected = True
                break
                
    def sqlSelect(self,sql,client):
        try:
            cr=client.cursor()
            cr.execute(sql)
            rs=cr.fetchall()
            result = []
            for row in rs:
                tmp = {}
                i = 0
                for desc in cr.description:
                    tmp[desc[0]] = row[i]
                    i+=1
                result.append(tmp)
            cr.close()
            return result
        except cx_Oracle.DatabaseError, e:
            #logging.error('sqlSelect error: %s', str(e))
            print "sqlSelect error: "+str(e)
            return None
            
    def format_msg(self,msg):
        #(179522323, '320282300000000024', datetime.datetime(2017, 5, 2, 21, 5, 36, 929000), '\xcb\xd5BZ781V', 2, 23, 'http://172.19.111.252:20000/pic?=d23idd6341ce3=t5-3m*=01p7i=d1s*i7d80*9d1=*6bdi3a77fd1e34f6f--8p3335i11b4e90-75739fd9')
        try:
            row = {}
            if len(msg['HPHM'])<7 or msg['HPHM']=='未识别' or msg['HPHM']=='无' or msg['HPHM']=='无车牌':
                row['license_plate'] = '无牌'
            else:
                row['license_plate'] = msg['HPHM'].strip()
                #row['license_plate'] = row['license_plate'].decode("gbk").encode('utf-8')
            #row['plate_type_id'] = tmp[6]
            row['unit_id'] = 2
            row['has_image'] = 1
            row['region_id'] = '320282'
            row["plate_type_id"]=int(msg["HPZL"])
            row['device_id'] = 0
            row['dev_id'] = 0
            row['lane_id'] = 0
            row['speed'] = int(msg['CLSD'])
            row['capture_time'] = msg['JGSK'].strftime('%Y-%m-%d %H:%M:%S')
            print row["capture_time"]
            if msg['XSFXDM']:
                row['direction_id'] = str(msg['XSFXDM'])
            else:
                row['direction_id'] = 0
            row['image_url'] = msg['QJTP']
            #row['plate_color'] = tmp[23] 
            
            row['location_id'] = msg['KKBH']
            row['loc_id'] = msg['KKBH']
            if row['location_id'] in self.location_to_yisa_location:
                row['location_id'] = self.location_to_yisa_location[row['location_id']]
            else:
                insert_id = self.execute_sql("INSERT INTO mon_location SET location_name = '%s',loc_id='%s',region_code='%s';" % ("",row['location_id'],row['region_id']),"insert")
                if insert_id > 0:
                    print insert_id,row['location_id']
                    r = requests.get('http://127.0.0.1:9002/?format=json&action=update_location_id')
                    r = requests.get('http://127.0.0.1:9002/?format=json&action=make_location_dict_cache')
                    self.load_cache()
                    if row['location_id'] in self.location_to_yisa_location:
                        row['location_id'] = self.location_to_yisa_location[row['location_id']]
            return row
        except Exception, e:
            logging.exception('格式化信息时错误: %s', str(e))
            return None
    def read_id(self,file_name):
        all_the_text = '0'
        try:
            file_object = open(file_name,'r')
            all_the_text = file_object.readline()
            file_object.close()
        except:
            pass
        return all_the_text

    def write_id(self,file_name,id):
        file_object = open(file_name, 'w')
        try:
            file_object.write(id)
        finally:
            file_object.close()
    def run(self):      
        config_file = open(os.path.dirname(os.path.abspath(__file__)) + '/config.yaml')
        self.config = yaml.safe_load(config_file)
        config_file.close()
        
        name = 'yisa_get_msg_oracle' 
        logging.basicConfig(level=logging.INFO) 
        handler = RotatingFileHandler('/var/log/%s.log' % name, maxBytes=134217728, backupCount=7)
        formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
        handler.setFormatter(formatter)
        logging.getLogger('').addHandler(handler)
        #-------------------同步输出到控制台-------------------
        # console = logging.StreamHandler()
        # console.setLevel(logging.INFO)
        # formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
        # console.setFormatter(formatter)
        # logging.getLogger().addHandler(console)
        #-------------------------------------------------------   
        logging.warning('启动 [%s]', name)
        self.connected = False
        self.db = None
        self.err_count = 0#统计设备id错误数
        self.err_dev_time = {}#统计设备时间错误数
        self.load_cache()
        cache = pylru.lrucache(600000)
        try:
            self.db = None
            r = redis.StrictRedis(unix_socket_path=self.config['redis_mq']['unix_socket_path'])
            pipe = r.pipeline()
            logging.warning('创建连接Oracle...')
            self.connect()
            while 1:
                print self.connected
                if not  self.connected:
                    self.connect()
                try:
                    min_id = int(self.read_id('hk_id.log').strip())
                    query = "SELECT max(CLXXBH) as MAXID from IVMS86X0.V_GCXX WHERE CLXXBH > %d" % min_id
                    print time.time()
                    rs = self.sqlSelect(query,self.client)
                    print time.time()
                    if rs == None or len(rs)==0:
                        time.sleep(5)
                        continue
                    if rs[0]['MAXID'] is None:
                        time.sleep(5)
                        continue
                        
                    max_id = int(rs[0]['MAXID'])
                    if min_id == 0:
                        min_id = max_id
                    if max_id - min_id > 500:
                        max_id = min_id + 500
                    self.write_id('hk_id.log',str(max_id))
                    print "max_id:%d",max_id
                    query = "SELECT CLXXBH , KKBH , JGSK , HPHM ,HPZL, XSFXDM , CLSD , QJTP from IVMS86X0.V_GCXX where  CLXXBH >= %d AND CLXXBH < %d" % (min_id,max_id)
                    print query
                    rs = self.sqlSelect(query,self.client)
                    if rs == None or len(rs)==0:
                        print 'no data'
                        time.sleep(5)
                        continue
                    start = timeit.default_timer()
                    for row in rs:
                        #print row
                        #if row['CLXXBH'] in cache:#过滤重复信息
                        #    continue
                        #else:
                        #    cache[row['CLXXBH']] = 1
                        msg = self.format_msg(row)
                        #print msg
                        if msg:
                            try:
                                #print msg
                                pipe.incr(time.strftime("%Y-%m-%d",time.localtime(time.time()))+":HIK")
                                pipe.rpush(self.config['redis_mq']['queue_key_name'], json.dumps(msg))
                                if pipe.__len__()==1:
                                    start = timeit.default_timer()
                            except UnicodeDecodeError,ude:
                                logging.error('编码json时错误: %s',msg['license_plate'])
                        if timeit.default_timer()-start>1 or pipe.__len__()>100:#1秒或100条入redis一次
                            pipe.execute()
                except KeyboardInterrupt:
                    logging.error('Ctrl+C,终止运行')
                    return
                except Exception, e:
                    logging.exception('处理数据时错误: %s', str(e))
                    time.sleep(10)
        except Exception, e:
            logging.exception('取数据时错误: %s', str(e))
            sys.exit(0)
    
if __name__ == "__main__":
    daemon = MyDaemon('/var/run/yisa_get_msg_oracle.pid')
    # daemon.run()
    # sys.exit(0)
    if len(sys.argv) == 2:
        if 'start' == sys.argv[1]:
            daemon.start()
        elif 'stop' == sys.argv[1]:
            daemon.stop()
        elif 'restart' == sys.argv[1]:
            daemon.restart()
        else:
            print "Unknown command"
            sys.exit(2)
        sys.exit(0)
    else:
        print "usage: %s start|stop|restart" % sys.argv[0]
        sys.exit(2)

